﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using Common;
using Business;
using System.Data.OracleClient;
using WF_Business;

namespace SbBusiness.Wsbs
{
    /// <summary>
    /// 办件流程实例操作类
    /// </summary>
    public class SerialInstance
    {
        /// <summary>
        /// 获取正在办理事项
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strName">申请人</param>
        /// <returns></returns>
        public DataTable GetZzblInstance(string strUserId, string strIID, string strName)
        {
            
            //包括预审通过的业务 or submitflag = '3' 
            string strSql = string.Format(@"select IID,
                                       FLOWNAME,
                                       flowtype,
                                       flowid,
                                       REQUESTER,
                                       SUBMITFLAG,
 '' tm,
       '' slh,
                                       to_char(INPUTDATE, 'yyyy-mm-dd') INPUTDATE,
                                       remark,
                                       to_char(INPUTDATE, 'yyyy-mm-dd') INPUTDATE,
                                       to_char(INPUTDATE, 'yyyy-mm-dd') edittime,
(select iid from xt_instance_ext b where b.关联id=a.iid and rownum<2) gliid,
                                       to_char(edittime, 'yyyy-mm-dd') FINISH_TIME
                                  from xt_submit_info a
                                 where (submitflag>=1 or submitflag=-4) 
 and not exists (select iid from st_instance m where m.iid=a.iid and m.status=2)
                                   and isdelete <> '1'
                                   and requester = '{0}'", strUserId);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and IID like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and REQUESTER like '%{0}%'", strName);
            }
            strSql += " order by iid desc";

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取正在办理事项个数
        /// </summary>
        /// <returns></returns>
        public string GetInstanceCount(string strUserId)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"SELECT COUNT(*)  from xt_submit_info a WHERE (submitflag>=1 or submitflag=-4) and REQUESTER = '{0}'  and not exists (select iid from st_instance m where m.iid=a.iid and m.status=2)", strUserId);
            
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取历史办件信息
        /// </summary>
        /// <param name="strUserId">申请人名称</param>
        /// <param name="strStatus">业务状态(0:未提交;1:已提交;2:已通过;3:预审;-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过)</param>
        /// <param name="strIID">办件编号</param>
        /// <returns></returns>
        public DataTable GetCompleteInstance(string strUserId, string strStatus, string strIID)
        {
            string strSql = string.Format(@"select a.IID,d.flowtype,d.flowname,NAME AS REQUESTER,
       '' tm,
       '' slh,
       to_char(a.createtime, 'yyyy-mm-dd') INPUTDATE,
       to_char(ACCEPTED_TIME, 'yyyy-mm-dd') edittime,
       '审核完成' remark,
       d.submitflag,
       to_char(FINISH_TIME, 'yyyy-mm-dd') FINISH_TIME
  from st_instance a, xt_submit_info d
 where a.status = 2
   and a.iid = d.iid
   and d.REQUESTER = '{0}'
   and a.iid in (select b.iid
                   from xt_instance_ext b
                  where b.status in ({1}))", strUserId, strStatus);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and a.IID like '%{0}%'", strIID);
            }
            strSql += " order by ACCEPTED_TIME desc";

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取不予受理案件信息
        /// </summary>
        /// <param name="strUserId">申请人名称</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strStatus">申请业务状态（0:未提交;2:已办结;-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过）</param>
        /// <returns></returns>
        public DataTable GetNoPass(string strUserId, string strIID, string strStatus)
        {
            string strSql = string.Format(@"select d.IID,d.flowtype,d.flowname,NAME AS REQUESTER,
       '' tm,
       '' slh,
       to_char(a.createtime, 'yyyy-mm-dd') INPUTDATE,
       to_char(ACCEPTED_TIME, 'yyyy-mm-dd') edittime,
       d.remark,
       d.submitflag,
       to_char(FINISH_TIME, 'yyyy-mm-dd') FINISH_TIME
  from st_instance a, xt_instance_ext c, xt_submit_info d
 where a.iid = c.iid
   and c.关联ID = d.iid
   and d.REQUESTER = '{0}'", strUserId);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and d.IID like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strStatus))
            {
                strSql += string.Format(" and c.status={0}", strStatus);
            }
            strSql += " order by ACCEPTED_TIME desc";

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 按业务流程状态获取办理事项个数
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <param name="strStatus">提交状态(0:未提交;2:已办结;-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过)</param>
        /// <returns></returns>
        /// <!--//去掉or status = -2条件 不包括被驳回的事项 update by zhongjian 20100316-->
        public string GetHistoryCount(string strUserId,string strStatus)
        {
            string strSql = string.Format(@"select count(*)
                                              from st_instance a
                                             where a.isdelete <> 1
                                               and a.iid in (select b.iid
                                                               from xt_instance_ext b, xt_submit_info c
                                                              where b.关联ID = c.iid
                                                                and b.status = '{1}'
                                                                and c.REQUESTER = '{0}')", strUserId, strStatus);

            if (strStatus != "-3")
            {
                strSql += " and status='2'";
            }

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取驳回办理事项
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strName">申请人</param>
        /// <returns></returns>
        public DataTable GetBoHuiHistory(string strUserId, string strIID, string strName)
        {
            string strSql = string.Format(@"select a.IID,
                   b.stop_remark remark,
                   d.flowtype,
                   d.FLOWNAME,
                   a.WID,
                   c.sync_type,
'' tm,
       '' slh,
                   NAME AS REQUESTER,c.submitflag,
                   to_char(c.inputdate, 'yyyy-mm-dd') AS INPUTDATE,
                   to_char(ACCEPTED_TIME, 'yyyy-mm-dd') edittime,
                   to_char(FINISH_TIME, 'yyyy-mm-dd') FINISH_TIME
              from st_instance        a,
                   xt_instance_ext    b,
                   xt_submit_info     c,
                   xt_workflow_define d
             where a.status = -2
               and a.iid = b.iid
               and b.关联id = c.iid
               and a.iid in (select b.iid
                               from xt_instance_ext b, xt_submit_info c
                              where b.关联ID = c.iid
                                and c.REQUESTER = '{0}')
               and c.flowname = d.flowname
               and d.ispub = 1
               and d.isdelete = 0", strUserId);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and a.IID like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and NAME like '%{0}%'", strName);
            }
            strSql += "  order by ACCEPTED_TIME desc";

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取驳回办理事项个数
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <returns></returns>
        public string GetBoHuiCount(string strUserId)
        {
            string strSql = string.Format(@"select count(*) from st_instance a where status=-2 and a.isdelete <>1
                and a.iid in (select b.iid from 
                 xt_instance_ext b,xt_submit_info c where b.关联ID=c.iid and c.REQUESTER='{0}')",
                strUserId);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取累计收件个数
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <returns></returns>
        public string GetShoujianCount(string strUserId)
        {
            string strSql = string.Format(@"select count(*) from xt_submit_info c where c.REQUESTER='{0}' and isdelete <>'1'", strUserId);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取所有累计收件个数
        /// </summary>
        /// <returns></returns>
        public string GetShoujianTotal()
        {
            string strSql = @"select count(iid) from xt_submit_info c where isdelete <>1";
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取累计办结个数
        /// </summary>
        /// <returns></returns>
        public string GetBanjieCount(string strUserId)
        {
            string strSql = string.Format(@"select count(*) from st_instance a where 
                (STATUS=2 or STATUS=-2) and a.iid in (select b.iid from 
                 xt_instance_ext b,xt_submit_info c where b.关联ID=c.iid and c.REQUESTER='{0}')",
                 strUserId);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取所有累计办结个数
        /// </summary>
        /// <returns></returns>
        public string GetBanjieTotal()
        {
            string strSql = @"select count(*)
                  from st_instance a
                 where (STATUS = 2 or STATUS = -2) and a.isdelete <>1";
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 按业务编号获取正在办理业务详细信息
        /// </summary>
        /// <param name="strSerial">业务编号</param>
        /// <returns></returns>
        public DataTable GetSerialList(string strSerial)
        {
            string strSql = string.Format(@"select 编号 SERIAL,
                   岗位编号 办理环节编号,
                   经办人   办理人员,
                   岗位     办理处室,
                   接件时间 办理时间,
                   办理操作,
                   isback   是否回退,
                   b.status
              FROM sv_work_item_list a, st_instance b
             where a.编号 = b.iid and b.iid='{0}'
             order by 办理时间 asc", strSerial);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取用户密码
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public string GetUserPWD(string strUserID)
        {
            string strSql = string.Format(@"select userpwd from sys_user where userid='{0}'", strUserID);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取当前用户消息提示步骤名称
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public string GetStepName(string strUserID)
        {
            string strSql = string.Format(@"select step_name from xt_request_step where step_no=
                            (select stepno from sys_user where userid='{0}')", strUserID);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 按申请编号(appid)获取流程IID（当为地信中心申请的业务时用）
        /// </summary>
        /// <param name="strIID">appid</param>
        /// <returns></returns>
        public string GetUtDxzxIID(string strIID)
        {
            string strSql = string.Format(@"select iid from UT_秘密基础测绘成果使用申请表 where appid={0}", strIID);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取用户有权限办理的事项
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public DataTable GetNewWork(string strUserID)
        {
            string strSql = string.Format(@"select a.id, b.flowname, b.flowtype, b.interfaceurl, b.interfacetype
                                              from SYS_USER_SERIAL a, xt_workflow_define b
                                             where is_pass = 1
                                               and a.serial_name = b.flowname
                                               and b.isdelete = '0'
                                               and b.ispub = '1'
                                               and UserID = '{0}'
                                             order by serial_time desc", strUserID);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据wid获取fid
        /// </summary>
        /// <param name="strFormName">流程名称</param>
        /// <returns></returns>
        public string GetFirstFidByWname(string strFormName)
        {
            string strSql = string.Format(@"select fid
                  from st_form a where a.rot=0
                   and a.fname like '%{0}%'", strFormName);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取最新的五条行政许可信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetResultInstanceInfo()
        {
            string strSql = @"select * from (select a.iid 案件编号,
                           a.name 申请人,
                           (case
                             when a.status = 1 then
                              '正在办理'
                             when a.status = 2 then
                              '审批通过'
                             when a.status = -2 then
                              '审批未通过'
                           end) 审核状态
                            from st_instance a where (a.status=2 or a.status=-2) order by a.iid desc) where rownum<=5";
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// Gets the instance result info.(add by lj)
        /// </summary>
        /// <param name="strIid">The STR iid.</param>
        /// <param name="strRequestStartDate">The STR request start date.</param>
        /// <param name="strRequestEndDate">The STR request end date.</param>
        /// <param name="strWid">The STR wid.</param>
        /// <param name="strRequester">The STR requester.</param>
        /// <returns></returns>
        public DataTable GetInstanceResultInfo(string strIid, string strRequestStartDate,
            string strRequestEndDate, string strWid)
        {
            string strSql = @"select iid 受理编号,
                           name 申请单位,
                           (case
                             when status = 1 then
                              '正在办理'
                             when status = 2 then
                              '通过'
                             when status = -2 then
                              '未通过'
                           end) 办件状态,
                            to_char(accepted_time,'yyyy-mm-dd') 申请日期,
                            to_char(exendtime,'yyyy-mm-dd') 办结日期
                            from st_instance a,st_workflow b 
                            where a.wid=b.wid and (status=2 or status=-2) and a.isdelete <>1";
            //to_char(accepted_time,'yyyy-mm-dd') 受理时间
            if (!string.IsNullOrEmpty(strIid))
            {
                strSql += string.Format(" and iid like '%{0}%'", strIid);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            if (!string.IsNullOrEmpty(strWid))
            {
                strSql += string.Format(" and a.wid='{0}'", strWid);
            }
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 查询政许可信息
        /// </summary>
        /// <param name="strIid">案件编号</param>
        /// <param name="strName">申请人</param>
        /// <param name="strFlow">办理事项</param>
        /// <param name="strRequestStartDate">受理时间(开始)</param>
        /// <param name="strRequestEndDate">受理时间(结束)</param>
        /// <returns></returns>
        public DataTable GetInstanceInfo(string strIid, string strName, string strFlow, string strRequestStartDate, string strRequestEndDate)
        {
            string strSql = @"select rownum 序号,iid 办件编号,
(select slbh from ut_地图审核受理通知书 a where iid=a.iid and input_index=0 and rownum<2) 受理编号,
                            c.flowtype 受理事项,
                           name 申请单位,
                           (case
                             when status = 1 then
                              '正在办理'
                             when status = 2 then
                              '通过'
                             when status = -2 then
                              '未通过'
                           end) 办件状态,
                            to_char(accepted_time,'yyyy-mm-dd') 申请日期,
                            to_char(exendtime,'yyyy-mm-dd') 应办结日期                           
                            from st_instance a,st_workflow b,xt_workflow_define c
                            where a.wid=b.wid  and a.isdelete <>1
                            and b.wname=c.flowname and c.ispub=1 and c.isdelete=0 ";

            if (!string.IsNullOrEmpty(strIid))
            {
                //strSql += string.Format(" and iid like '%{0}%'", strIid);

                strSql += string.Format(" and iid in (select iid from ut_地图审核受理通知书 a where slbh like '%{0}%')", strIid);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and name like '%{0}%'", strName);
            }
            if (!string.IsNullOrEmpty(strFlow))
            {
                strSql += string.Format(" and flowtype like '%{0}%'", strFlow);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            strSql += " order by accepted_time desc";

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取结果公示信息
        /// </summary>
        /// <param name="strIid">案件编号</param>
        /// <param name="strName">申请人</param>
        /// <param name="strFlow">办理事项</param>
        /// <param name="strRequestStartDate">受理时间(开始)</param>
        /// <param name="strRequestEndDate">受理时间(结束)</param>
        /// <returns></returns>
        public DataTable GetJggs(string strIid, string strName, string strFlow, string strRequestStartDate, string strRequestEndDate)
        {
            string strSql = @"select t.iid 办件编号,
       m.批准号 审图号,
       name 申请单位,
       图名,
       to_char(finish_time, 'yyyy-mm-dd') 批准日期
  from st_instance t, ut_国家测绘局地图审核申请表 m
 where t.iid = m.iid
   and m.input_index = 0
   and t.status = 2
   and m.批准号 is not null";

            if (!string.IsNullOrEmpty(strIid))
            {
                //strSql += string.Format(" and iid like '%{0}%'", strIid);

                strSql += string.Format(" and m.批准号 like '%{0}%'", strIid);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and name like '%{0}%'", strName);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            strSql += " order by accepted_time desc";

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// Gets the serial info.
        /// </summary>
        /// <param name="strIid">The STR iid.</param>
        /// <param name="strRequestStartDate">The STR request start date.</param>
        /// <param name="strRequestEndDate">The STR request end date.</param>
        /// <param name="strWid">The STR wid.</param>
        /// <param name="strRequester">The STR requester.</param>
        /// <returns></returns>
        public DataTable GetMainSerialInfo(string strIid, string strRequestStartDate,
            string strRequestEndDate, string strWid, string strRequester)
        {
            string strSql = string.Format(@"select rownum 序号,a.iid 案件编号,
                   a.name 申请人,
                   (case
                     when a.status = 1 then
                      '正在办理'
                     when a.status = 2 then
                      '审批通过'
                     when a.status = -2 then
                      '审批未通过'
                   end) 审核状态,
                    (select distinct step from st_work_item b where 
                    b.iid=a.iid and (b.active = 1 or b.active = 0) and rownum<2) 所在岗位
                    from st_instance a where a.iid in (select b.iid from 
                 xt_instance_ext b,xt_submit_info c where b.关联ID=c.iid and c.REQUESTER='{0}') and a.isdelete <>1", strRequester);
            if (!string.IsNullOrEmpty(strIid))
            {
                strSql += string.Format(" and iid like '%{0}%'", strIid);
            }
            if (!string.IsNullOrEmpty(strRequestStartDate))
            {
                strSql += string.Format(" and accepted_time>=to_date('{0}','yyyy-mm-dd')", strRequestStartDate);
            }
            if (!string.IsNullOrEmpty(strRequestEndDate))
            {
                strSql += string.Format(" and accepted_time<=to_date('{0}','yyyy-mm-dd')", strRequestEndDate);
            }
            if (!string.IsNullOrEmpty(strWid))
            {
                strSql += string.Format(" and a.wid='{0}'", strWid);
            }

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据name获取value
        /// </summary>
        /// <param name="strName">The STR name.</param>
        /// <returns></returns>
        public string GetValue(string strName)
        {
            string strSql = string.Format(@"select SEED_VALUE from st_number_seed t where SEED_NAME='{0}'", strName);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 修改VALUE
        /// </summary>
        /// <param name="strName">name</param>
        /// <param name="decValue">value</param>
        /// <returns></returns>
        public int UpdateValue(string strName, decimal decValue)
        {
            string strSql = string.Format(@"update st_number_seed set SEED_VALUE={1} where SEED_NAME='{0}'", strName, decValue);

            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取留言咨询信息
        /// </summary>
        /// <param name="strID">ID</param>
        /// <param name="isPub">是否公布</param>
        /// <param name="iNum">查询条数</param>
        /// <param name="hHidWordLen">隐藏字符</param>
        /// <param name="strType">留言类型</param>
        /// <param name="strTitle">留言主题</param>
        /// <param name="strMsgCode">留言码</param>
        /// <param name="strFromDate">开始日期</param>
        /// <param name="strToDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetLyzxInfo(string strID, bool isPub, int iNum, int hHidWordLen, string strType, string strTitle, string strMsgCode,
            string strFromDate, string strToDate)
        {
            string strSql = string.Format(@"select * from (select id,can_sync,sync_type,name,email,sex,address,dh,slbm,notes,sj,wtlx,sfgk,
                createdate,msshowtype,
                (case when length(zt)>{0} then Substr(zt,0,{0})|| '...' else zt end ) title,
                (case when wtlx = 1 then '咨询'
                      when wtlx = 2 then '建议'
                      when wtlx = 3 then '投诉'
                      when wtlx = 3 then '求助'
                      when wtlx = 3 then '其它'
                 end) TypeText
                from xt_lyzx where msshowtype=1 ", hHidWordLen);
            if (!string.IsNullOrEmpty(strID))
                strSql += string.Format(" and id='{0}'", strID);

            if (isPub)
            {
                strSql += string.Format(" and sfgk='公开'");//是否公开

                if (!string.IsNullOrEmpty(strType))//问题类型
                {
                    int iType = int.Parse(strType);
                    if (iType > 0)
                        strSql += string.Format(" and wtlx='{0}'", strType);
                }

                if (!string.IsNullOrEmpty(strTitle))//主题
                    strSql += string.Format(" and xt like '%{0}%'", strTitle);
                if (!string.IsNullOrEmpty(strFromDate))//开始日期
                    strSql += string.Format(" and createdate>to_date('{0}','yyyy-mm-dd')", strFromDate);
                if (!string.IsNullOrEmpty(strToDate))//结束日期
                    strSql += string.Format(" and createdate<to_date('{0}','yyyy-mm-dd')", strToDate);
            }
            else//按留言码查询
            {
                if (!string.IsNullOrEmpty(strMsgCode))
                    strSql += string.Format("and sfgk='不公开' and MsgCode='{0}'", strMsgCode);
            }
            strSql += string.Format(" order by createdate desc) ");
            if (iNum > 0)
                strSql += string.Format(" where rownum <= {0}", iNum);

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取留言咨询回复信息
        /// </summary>
        /// <param name="strLYID">留言咨询ID</param>
        /// <returns></returns>
        public DataTable GetLyzxFKInfo(string strLYID)
        {
            string strSql = string.Format(@"select a.id,a.fk_notes,fk_date,b.user_name,fk_userid
                            from xt_lyzx_fk a ,st_user b where a.fk_userid=b.userid and  ly_id='{0}' order by fk_date", strLYID);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据条件获取常见问题信息(xt_other)
        /// </summary>
        /// <param name="strID">ID</param>
        /// <param name="strType">信息大类</param>
        /// <param name="strSmaillType">信息小类</param>
        /// <param name="iNum">查询条数</param>
        /// <param name="hHidWordLen">隐藏字符</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091028-->
        public DataTable GetAskQuestion(string strID, string strType, string strSmaillType,
            int iNum, int hHidWordLen)
        {
            string strSql = string.Format(@"select id,(case when length(t.title)>{0} then 
                Substr(t.title,0,{0}) || '...' else t.title end ) as title,content,
                to_char(time,'yyyy-mm-dd') CreateTime,type,is_notice,smalltype,can_sync,sync_type
                    from xt_other t where 1=1 ", hHidWordLen);
            if (!string.IsNullOrEmpty(strID))
                strSql += string.Format(" and id='{0}'", strID);
            if (!string.IsNullOrEmpty(strType))
                strSql += string.Format(" and type='{0}'", strType);//信息大类
            if (!string.IsNullOrEmpty(strSmaillType))
                strSql += string.Format(" and smalltype='{0}'", strSmaillType);//信息小类
            if (iNum > 0)
                strSql += string.Format(" and rownum <= {0}", iNum);

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取IID号
        /// </summary>
        /// <param name="wid"></param>
        /// <param name="userId"></param>
        /// <param name="ownerName"></param>
        /// <returns></returns>
        public long GetIIDWithEnterprise(string wid, string userId, string ownerName)
        {
            return WF_Business.SeedNumber.GetIIDWithEnterprise("", "", "");
        }
       
        
    }
}
